//
//  SQLHelper.m
//  LearningFMDB
//
//  Created by dihuijun on 17/9/12.
//  Copyright © 2017年 Cycle. All rights reserved.
//

#import "SQLHelper.h"
#import <FMDB/FMDB.h>

#import "Person.h"
#import "Car.h"

//#define filePath [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject]stringByAppendingPathComponent:@"pss.sqlite"]
//#define filePath [[NSBundle mainBundle]pathForResource:@"pss.sqlite" ofType:nil]
static SQLHelper *_database = nil;

@interface SQLHelper ()<NSCopying,NSMutableCopying>
{
    FMDatabase *_db;
    NSMutableArray *_picArray;
}



@end


@implementation SQLHelper


/** 初始化 */
+ (instancetype)sharedSQLHelper{
    if (_database == nil) {
        _database = [[SQLHelper alloc]init];
        
        //初始化数据库
        [_database initDataBase];
    }
    return _database;
}
+(instancetype)allocWithZone:(struct _NSZone *)zone{
    if (_database == nil) {
        _database = [super allocWithZone:zone];
        
    }
    return _database;
}
-(id)copy{
    return self;
}
-(id)mutableCopy{
    return self;
}
-(id)copyWithZone:(NSZone *)zone{
    return self;
}
-(id)mutableCopyWithZone:(NSZone *)zone{
    return self;
}








#pragma mark - 获取数据库文件的全路径
- (NSString *)getFullDatabasePathFromFileName:(NSString *)name{
    NSString *path = NSHomeDirectory();
    NSString *docPath = [path stringByAppendingPathComponent:@"Documents"];
    NSFileManager *fm = [NSFileManager defaultManager];
    if ([fm fileExistsAtPath:docPath]) {
        return [docPath stringByAppendingFormat:@"/%@",name];
    }else{
        NSLog(@"Documents不存在");
        return nil;
    }
}


#pragma mark ---  1.initDataBase
- (void)initDataBase
{
    //获取Documents目录路径
//    NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];
//    //创建文件路径
//    NSString *Path = [documentsPath stringByAppendingPathComponent:@"dd.db"];
    _picArray = [NSMutableArray arrayWithObjects:@"p1.png",@"p2.jpg", nil];
    //实例化FMDataBase对象
    _db = [FMDatabase databaseWithPath:[self getFullDatabasePathFromFileName:@"AppData.db"]];
    //打开数据库
    [_db open];
    //初始化数据结构表(有几张表就创建几张)autoincrement
    NSString *personSql = @"CREATE TABLE 'person' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,'person_id' VARCHAR(255),'person_name' VARCHAR(255),'person_age' VARCHAR(255),'person_number' VARCHAR(255),'person_image' VARCHAR(255))";
    NSString *carSql = @"CREATE TABLE 'car' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,'own_id' VARCHAR(255),'car_id' VARCHAR(255),'car_brand' VARCHAR(255),'car_price' VARCHAR(255))";
    
    //执行数据库
    [_db executeUpdate:personSql];
    [_db executeUpdate:carSql];
    ///初始化数据
    [self initDataSource:10];
    //关闭数据库
    [_db close];
    
}
- (void)initDataSource:(NSInteger)count{
    for (NSInteger i = 0; i<count; i++) {
        //姓名
        int nameRandom = arc4random_uniform(1000);
        NSString *name = [NSString stringWithFormat:@"person_%d 号",nameRandom];
        //头像
        NSInteger headRandowIndex = arc4random_uniform(_picArray.count);
        UIImage *img = [UIImage imageNamed:_picArray[headRandowIndex]];
        //年龄
        NSInteger ageRandom = arc4random_uniform(100)+1;
        NSInteger age = ageRandom;
        
        //创建person类
        Person *person       = [Person new];
        person.name          = name;
        person.age           = age;
        person.headImageData = UIImagePNGRepresentation(img);
        //添加到数据库
        [[SQLHelper sharedSQLHelper]addPerson:person];
    }
}






#pragma mark --- person类
/**
 *  添加person
 */
- (void)addPerson:(Person *)person{
    //1.打开数据库
    [_db open];
    
    
    NSNumber *maxID = @(0);
    //2.查询person数据库
    FMResultSet *result = [_db executeQuery:@"SELECT * FROM person"];
    //2.1 获取数据库中最大的ID
    while ([result next]) {
        if ([maxID integerValue] < [[result stringForColumn:@"person_id"]integerValue]) {
            //获取数据库里面的最大的userID
            maxID = @([[result stringForColumn:@"person_id"]integerValue]);
            
            //NSLog(@"person_maxID : %@",maxID);
        }
    }
    //因为需要再添加一条信息，因此这个ID需要＋1，防止添加的ID跟数据库的最大ID一样
    maxID = @([maxID integerValue]+1);
    //3.执行插入
    NSString *sql = @"INSERT INTO person(person_id,person_name,person_age,person_number,person_image) VALUES(?,?,?,?,?)";
    BOOL flg = [_db executeUpdate:sql,maxID,person.name,@(person.age),@(person.number),person.headImageData];
    if (flg) {
        NSLog(@"插入成功");
    } else {
        NSLog(@"插入失败");
    }
    //4.关闭数据库
    [_db close];
    
}
/**
 * 为person修改头像
 */
- (void)updatePersonHeadImage:(Person *)p{
    if([_db open]){
        BOOL flg = [_db executeUpdate:@"UPDATE person SET person_image = ? where person_id = ?",p.headImageData,p.ID];
        if (flg) {
            NSLog(@"修改成功");
        } else {
            NSLog(@"修改失败");
        }
        
        [_db close];
    } else {
        NSLog(@"数据库没打开");
    }
    
    
}
/**
 *  删除person
 */
- (void)deletePerson:(Person *)person{
    //1.打开数据库
    if([_db open]){
        //查询该条记录是否存在
        NSString *selectSql = [NSString stringWithFormat:@"SELECT * FROM person WHERE person_id = %@",person.ID];
        FMResultSet *set = [_db executeQuery:selectSql];
        if ([set next]) {
            //来到这里说明数据存在,用的if,不是while
            //2.执行删除语句
            BOOL flg = [_db executeUpdate:@"DELETE FROM person WHERE person_id = ?",person.ID];
            if (flg) {
                NSLog(@"删除成功");
            } else {
                NSLog(@"删除失败");
            }
        } else {
            NSLog(@"未查到该条消息");
        }
        
        //3.关闭数据库
        [_db close];
    } else {
        NSLog(@"数据库没打开");
    }
    
}
/**
 *  更新person
 */
- (void)updatePerson:(Person *)person{
    //1.打开数据库
    [_db open];
    //2.更新数据(
    //2.1 注意,修改数据的时候先查询该数据是否存在
    NSString *selectSql = [NSString stringWithFormat:@"SELECT * FROM person WHERE person_id = %@",person.ID];
    FMResultSet *set = [_db executeQuery:selectSql];
    if ([set next]) {
        //来到这里说明数据存在,用的if,不是while
        //2.2 执行修改语句
        BOOL flg = [_db executeUpdate:@"UPDATE person SET person_image = ?,person_name = ?,person_age = ?,person_number = ? WHERE person_id = ?",person.headImageData,person.name,person.age,@(person.number+1),person.ID];
        if (flg) {
            NSLog(@"更新成功");
        } else {
            NSLog(@"更新失败");
        }
    } else {
        NSLog(@"未查到该条消息");
    }
    
   
    //3.关闭数据库
    [_db close];
}
/**
 * 获取所有数据
 */
- (NSMutableArray<Person *> *)getAllPerson{
     //1.打开数据库
     [_db open];
     //2.查询
    NSMutableArray *dataArray = [NSMutableArray array];
    
    FMResultSet *result = [_db executeQuery:@"SELECT * FROM person"];
    while ([result next]) {
        Person *person = [[Person alloc]init];
        person.ID = @([[result stringForColumn:@"person_id"]integerValue]);
        person.name = [result stringForColumn:@"person_name"];
        person.age = [[result stringForColumn:@"person_age"]integerValue];
        person.number = [[result stringForColumn:@"person_number"]integerValue];
        person.headImageData = [result dataForColumn:@"person_image"];
        [dataArray addObject:person];
    }
     //3.关闭数据库
     [_db close];
    
    //4.返回数据
    return dataArray;
}



#pragma mark --- Car
/**
 *  给Person 添加车辆
 */
- (void)addCar:(Car *)car toPerson:(Person *)person{
    //1.打开数据库
    [_db open];
    //根据person_id是否拥有car来添加car_id
    NSNumber *maxID = @(0);
    
//    FMResultSet *res = [_db executeQuery:@"SELECT * FROM car where own_id = ?",person.ID];
     FMResultSet *res = [_db executeQuery:[NSString stringWithFormat:@"SELECT * FROM car where own_id = %@",person.ID]];
    while ([res next]) {
        if ([maxID integerValue] < [[res stringForColumn:@"car_id"]integerValue]) {
            maxID = @([[res stringForColumn:@"car_id"]integerValue]);
           // NSLog(@"car_maxID : %@",maxID);
        }
    }
    maxID = @([maxID integerValue]+1);
    //执行添加
    BOOL flg = [_db executeUpdate:@"INSERT INTO car (own_id,car_id,car_brand,car_price)VALUES(?,?,?,?)",person.ID,maxID,car.brand,@(car.price)];
    if (flg) {
        NSLog(@"添加车车成功");
    } else {
        NSLog(@"添加车车删除失败");
    }
    //3.关闭数据库
    [_db close];
}
/**
 * 给Person 删除车辆
 */
- (void)deleteCar:(Car *)car fromPerson:(Person *)person{
    //1.打开数据库
    [_db open];
    
    //2.执行删除
   BOOL flg = [_db executeUpdate:@"DELETE FROM car WHERE own_id = ? and car_id = ?",person.ID,car.car_id];
    if (flg) {
        NSLog(@"删除车车成功");
    } else {
        NSLog(@"删除车车失败");
    }
    //3.关闭数据库
    [_db close];
}
/**
 *  获取person的所有车辆
 */
- (NSMutableArray<Car *> *)getAllCarsFromPerson:(Person *)person{
    //1.打开数据库
    [_db open];
    //2.获取车辆
    NSMutableArray *carArray = [NSMutableArray array];
    
//    FMResultSet *res = [_db executeQuery:@"SELECT * FROM car WHERE own_id = ?",person.ID];
        FMResultSet *res = [_db executeQuery:[NSString stringWithFormat:@"SELECT * FROM car WHERE own_id = %@",person.ID]];
    while ([res next]) {
        Car *car = [[Car alloc]init];
        car.own_id = person.ID;
        car.car_id = @([[res stringForColumn:@"car_id"]integerValue]);
        car.brand = [res stringForColumn:@"car_brand"];
        car.price = [[res stringForColumn:@"car_price"]integerValue];

        [carArray addObject:car];
    }
    
    //3.关闭数据库
    [_db close];
    //4.返回car
    return carArray;
}
/**
 *  删除person的所有车辆
 */
- (void)deleteAllCarsFromPerson:(Person *)person{
    //1.打开数据库
    [_db open];
    //2.执行删除
    BOOL flg = [_db executeUpdate:@"DELETE FROM car WHERE own_id = ?",person.ID];
    if (flg) {
        NSLog(@"删除所有车车成功");
    } else {
        NSLog(@"删除所有车车失败");
    }
    //3.关闭数据库
    [_db close];
}




@end
